/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package SQLDAL;

import DALFactory.IDataMapper;
import Entity.EHouse;
import Entity.EUser;
import java.sql.ResultSet;
import java.util.HashMap;

/**
 *
 * @author kocio
 */
public class SQLDataMapper implements IDataMapper {

    /**
     * 从数据库中取出所有的EHouse对象存入内存
     */
    private HashMap mapEHouse() {
        HashMap houseMap = new HashMap();
        String sql = "SELECT * FROM [RH].[dbo].[House]";
        SQLStatement sqlstmt = new SQLStatement();
        SQLConnection sc = sqlstmt.openSQLConnection();
        ResultSet rs = sqlstmt.executeQuery(sc, sql);
        try {
            while (rs.next()) {
                EHouse house = new EHouse();
                house.setObjectID("HID" + rs.getInt("HID"));
                house.setHID(rs.getInt("HID"));
                house.setHousePrice(rs.getInt("Price"));
                house.setHouseHoster(rs.getString("USERNAME"));
                house.setArea(rs.getFloat("Area"));
                house.setType(rs.getString("Type").trim());
                house.setFloor(rs.getInt("Floor"));
                house.setLocation(rs.getString("Location").trim());
                house.setPremise(rs.getString("Premise").trim());
                house.setArrow(rs.getString("Arrow").trim());
                houseMap.put(house.getObjectID(), house);
            }
        } catch (Exception ex) {
            ex.printStackTrace();
        } finally {
            sqlstmt.closeSQLConnection(sc);
        }
        return houseMap;
    }

    /**
     * 从数据库中取出所有的EUser对象存入内存
     */
    private HashMap mapUser() {
        HashMap userMap = new HashMap();
        String sql = "SELECT * FROM [RH].[dbo].[USER]";
        SQLStatement sqlstmt = new SQLStatement();
        SQLConnection sc = sqlstmt.openSQLConnection();
        ResultSet rs = sqlstmt.executeQuery(sc, sql);
        try {
            while (rs.next()) {
                EUser user = new EUser();
                user.setObjectID(rs.getString("USERNAME"));
                user.setUserID(rs.getInt("UID"));
                user.setUserName(rs.getString("USERNAME"));
                user.setEmail(rs.getString("E-mail"));

                userMap.put(user.getUserName(), user);
            }
        } catch (Exception ex) {
            ex.printStackTrace();
        } finally {
            sqlstmt.closeSQLConnection(sc);
        }
        return userMap;
    }

    /**
     * 获得缓存中的所有房间
     *
     * @return
     */
    public HashMap getHouseMap() {
        return this.mapEHouse();
    }

    /**
     * 获得缓存中的所有用户
     *
     * @return
     */
    public HashMap getUserMap() {
        return this.mapUser();
    }

    /**
     *
     * @param house
     */
    public void updateHouse(EHouse house) {
        String sql = "UPDATE [RH].[dbo].[House] SET Price='" + house.getHousePrice()
                + "',USERNAME='" + house.getHouseHoster()
                + "',Arrow='" + house.getArrow()
                + "',Type='" + house.getType()
                + "',Premise='" + house.getPremise()
                + "',Location='" + house.getLocation()
                + "',Floor='" + house.getFloor()
                + "' WHERE HID='" + house.getHID() + "'";

        SQLStatement sqlstmt = new SQLStatement();
        SQLConnection sc = sqlstmt.openSQLConnection();
        sqlstmt.executeUpdate(sc, sql);
        sqlstmt.closeSQLConnection(sc);
    }

    /**
     *
     * @param house
     */
    public void insertHouse(EHouse house) {
        String sql = "INSERT INTO [RH].[dbo].[House]("
                + "HID,Price,USERNAME,Area,Arrow.Premise,Floor,Location) "
                + "VALUES('" + house.getHID()
                + "','" + house.getHousePrice()
                + "','" + house.getHouseHoster()
                + "','" + house.getArea()
                + "','" + house.getArrow()
                + "','" + house.getPremise()
                + "','" + house.getFloor()
                + "','" + house.getLocation()
                + "')";
        SQLStatement sqlstmt = new SQLStatement();
        SQLConnection sc = sqlstmt.openSQLConnection();
        sqlstmt.executeUpdate(sc, sql);
        sqlstmt.closeSQLConnection(sc);
    }

    /**
     *
     * @param house
     */
    public void deleteHouse(EHouse house) {
        String sql = "DELETE FROM [RH].[dbo].[House] WHERE HID='" + house.getHID() + "'";
        SQLStatement sqlstmt = new SQLStatement();
        SQLConnection sc = sqlstmt.openSQLConnection();
        sqlstmt.executeUpdate(sc, sql);
        sqlstmt.closeSQLConnection(sc);
    }

    /**
     *
     * @param user
     */
    public void updateUser(EUser user) {
        String sql = "UPDATE [RH].[dbo].[USER] SET UID='" + user.getUserID() + "',PASSWD='" + user.getPassWD() + "',E-mail='" + user.getUserEmail() + "' WHERE USERNAME='" + user.getUserName() + "'";
        SQLStatement sqlstmt = new SQLStatement();
        SQLConnection sc = sqlstmt.openSQLConnection();
        sqlstmt.executeUpdate(sc, sql);
        sqlstmt.closeSQLConnection(sc);
    }

    public int insertUser(EUser user) {
        String sql = "INSERT INTO [RH].[dbo].[USER](USERNAME,PASSWD,E-mail) VALUES('" + user.getUserName() + "','" + user.getPassWD() + "'," + user.getUserEmail() + "')";
        SQLStatement sqlstmt = new SQLStatement();
        SQLConnection sc = sqlstmt.openSQLConnection();
        sqlstmt.executeUpdate(sc, sql);
        int UID=getUserByUserName(user.getUserName()).getUserID();
        sqlstmt.closeSQLConnection(sc);
        return UID;
    }

    public void deleteUser(EUser user) {
        String sql = "DELETE FROM [RH].[dbo].[USER] WHERE HID='" + user.getUserName() + "'";
        SQLStatement sqlstmt = new SQLStatement();
        SQLConnection sc = sqlstmt.openSQLConnection();
        sqlstmt.executeUpdate(sc, sql);
        sqlstmt.closeSQLConnection(sc);
    }

    /**
     *
     * @param hID
     * @return
     */
    public EHouse getHouseByHID(String hID) {
        String sql = "Select * From [RH].[dbo].[House] Where HID='" + hID + "'";
        SQLStatement sqlstmt = new SQLStatement();
        SQLConnection sc = sqlstmt.openSQLConnection();
        ResultSet rs = sqlstmt.executeQuery(sc, sql);
        try {
            if (rs.next()) {
                EHouse house = new EHouse();
                house.setObjectID("HID" + rs.getInt("HID"));
                house.setHID(rs.getInt("HID"));
                house.setHouseHoster(rs.getString("USERNAME"));
                house.setHousePrice(rs.getInt("HousePrice"));
                return house;
            } else {
                System.out.println("数据库中不存在你想要的EHouse对象");
                return null;
            }
        } catch (Exception ex) {
            ex.printStackTrace();
            return null;
        } finally {
            sqlstmt.closeSQLConnection(sc);
        }
    }

    /**
     *
     * @param userName
     * @return
     */
    public EUser getUserByUserName(String userName) {
        String sql = "Select * From [RH].[dbo].[USER]";
        SQLStatement sqlstmt = new SQLStatement();
        SQLConnection sc = sqlstmt.openSQLConnection();
        ResultSet rs = sqlstmt.executeQuery(sc, sql);
        try {
            if (rs.next()) {
                EUser user = new EUser();
                user.setObjectID(rs.getString("USERNAME"));
                user.setUserID(rs.getInt("UID"));
                user.setUserName(rs.getString("USERNAME"));
                user.setEmail(rs.getString("E-mail"));
                return user;
            } else {
                System.out.println("数据库中不存在你想要的EUser对象");
                return null;
            }
        } catch (Exception ex) {
            ex.printStackTrace();
            return null;
        } finally {
            sqlstmt.closeSQLConnection(sc);
        }
    }
}
